import re
import sys
import xlrd

from model.University import University
from model.UniversityMajorPlan import UniversityMajorPlan
from model.UniversityMajorPlanOcr import UniversityMajorPlanOcr
from model.UniversityMajorPlanTemp import UniversityMajorPlanTemp
from model.UniversityMajorRankMean import UniversityMajorRankMean


# 复制2022年的《招生计划》数据到2023年，因为正式的2023年招生计划还没出来
def to_copy():
    universityMajorPlan = UniversityMajorPlan()
    old_year = 2022
    new_year = 2023
    data = universityMajorPlan.getListAll(old_year)
    total_list = list()
    for index, row in enumerate(data):
        print(index, row)
        row['year'] = new_year
        del row['id']
        total_list.append(row)

    universityMajorPlan.toInsertBatch(total_list)


# 导入2023年《招生计划》数据
def import_university_major_plan():
    data = xlrd.open_workbook('../static/excel_2023/univerity_major_plan.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    universityMajorPlanOcr = UniversityMajorPlanOcr()
    before_row = dict()
    for i in range(rowNum):
        row = dict()
        if i > 0:
            for j in range(colNum):
                row['year'] = 2023
                if j == 0:
                    if table.cell(i, j).value:
                        row['page'] = int(table.cell(i, j).value)
                if j == 1:
                    row['batch'] = match_code(table.cell(i, j).value)
                if j == 2:
                    row['ucode'] = int(table.cell(i, j).value)
                if j == 3:
                    row['uname'] = table.cell(i, j).value
                if j == 4:
                    row['address'] = table.cell(i, j).value
                if j == 5:
                    row['tel'] = table.cell(i, j).value
                if j == 6:
                    if table.cell(i, j).value:
                        plan_number_str = table.cell(i, j).value
                        if isinstance(plan_number_str, str):
                            first_index = plan_number_str.find('人')
                            if first_index:
                                plan_number_str = plan_number_str.replace("人", "")
                                row['plan_number'] = int(plan_number_str)
                            else:
                                row['plan_number'] = int(plan_number_str)
                        else:
                            row['plan_number'] = int(plan_number_str)
                    else:
                        row['plan_number'] = 0
                if j == 7:
                    if table.cell(i, j).value:
                        row['major_code'] = table.cell(i, j).value
                    else:
                        row['major_code'] = ''
                if j == 8:
                    if table.cell(i, j).value:
                        row['remarks'] = table.cell(i, j).value
                    else:
                        row['remarks'] = ''
        if not bool(before_row):
            before_row = row
        elif (before_row['ucode'] == row['ucode']) and (before_row['major_code'] == row['major_code']):
            before_row['remarks'] = before_row['remarks'] + row['remarks']
        else:
            print('写入：', before_row)
            universityMajorPlanOcr.toInsert(before_row)
            before_row = row
    print("最后一行：", print(before_row))
    universityMajorPlanOcr.toInsert(before_row)


def match_code(code):
    if code == '本科第一批':
        ucode = 9
    elif code == '本科第一批预科':
        ucode = 10
    elif code == '本科第二批':
        ucode = 11
    elif code == '精准脱贫专项计划本科批':
        ucode = 12
    elif code == '本科第二批预科A类':
        ucode = 13
    elif code == '本科第二批预科B类':
        ucode = 14
    elif code == '高职高专普通批':
        ucode = 19
    else:
        ucode = 0
    return ucode


def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass
    try:
        import unicodedata
        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass

    return False


# 从university_major_plan_ocr取出初步识别的招生计划数据，通过正则匹配，过滤出有用的数据
def match_university_major_plan():
    university = University()
    universityMajorPlanTemp = UniversityMajorPlanTemp()
    universityMajorPlanOcr = UniversityMajorPlanOcr()
    year = 2023
    data = universityMajorPlanOcr.getList(year)
    total_list = list()
    for index, row in enumerate(data):
        print(row)
        insert = dict()
        insert['page'] = row['page']
        insert['year'] = row['year']
        insert['batch'] = row['batch']
        insert['ucode'] = row['ucode']
        insert['uname'] = row['uname'].rstrip('0123456789')
        insert['major_code'] = row['major_code']
        insert['plan_number'] = row['plan_number']
        insert['tel'] = row['tel']
        insert['remarks'] = row['remarks']
        insert['major'] = ''
        insert['major_type'] = ''
        insert['type'] = 0
        insert['cut_remarks'] = ''
        insert['province_code'] = 0
        insert['city_code'] = 0
        insert['province'] = ''
        insert['city'] = ''
        insert['plan_years'] = 0
        insert['plan_expenses'] = 0
        if row['remarks']:
            remark = row['remarks']
            # 去除字符串所有空格
            remark_new = remark.replace(" ", "")
            # print('remark_new：', remark_new)
            # 匹配 xx人
            f1 = re.findall(r'(\d+人)', remark_new)
            # print('f1：', f1)
            if f1:
                insert = match_fun(remark_new, f1, insert, row['address'])
                total_list.append(insert)
            else:
                # 如若匹配不到xx人，则匹配理科类
                f2 = re.findall(r'(理工)', remark_new)
                if f2:
                    print("f2：", f2)
                    insert = match_fun_2(remark_new, f2, insert, row['address'])
                    total_list.append(insert)
                else:
                    f3 = re.findall(r'(文史)', remark_new)
                    if f3:
                        print("f3：", f3)
                        insert = match_fun_2(remark_new, f3, insert, row['address'])
                        total_list.append(insert)
        else:
            # 根据院校代码，查询院校库university_2，是否存在，存在则取出该院校的所在省市地址
            university_row = university.getOneByCode(insert['ucode'])
            if university_row:
                insert['province_code'] = university_row['province_code']
                insert['city_code'] = university_row['city_code']
                insert['province'] = university_row['province']
                insert['city'] = university_row['city']
            else:
                insert['province_code'] = 0
                insert['city_code'] = 0
                insert['province'] = ''
                insert['city'] = row['address']
            insert['plan_number'] = 0
            insert['city'] = row['address']
            insert['type'] = 1
            universityMajorPlanTemp.toInsert(insert)
            insert['type'] = 2
            universityMajorPlanTemp.toInsert(insert)
    universityMajorPlanTemp.toInsertBatch(total_list)  # 先添加到临时中间表，核对过中间表可行，再切换添加到正式表


def match_fun(remark_new, f, insert, address):
    university = University()
    num_people = f[-1]  # 匹配xx人之后，返回的f1是一个列表，取f1列表最后一个元素
    # print('num_people：', num_people)
    # print(remark_new.split(num_people))

    # 匹配专业
    major = remark_new.split(num_people)[0:1][0]  # xx人之前，为专业
    # print('major：', major)
    if major:
        if insert['major_code'] and major.find(insert['major_code']) != -1:
            major = major.replace(insert['major_code'], '')
    # print('major_final：', major)
    insert['major'] = major

    # 匹配专业文理科
    res = remark_new.split(num_people)[1:][0]  # 从 xx人的地方，分割字符串为两半，然后去后一半
    if res.find('理工') != -1:
        insert['major_type'] = '理工类'
        insert['type'] = 1
    elif res.find('文史') != -1:
        insert['major_type'] = '文史类'
        insert['type'] = 2
    else:
        insert['major_type'] = ''
        insert['type'] = 0
    # print('专业类型：', insert['major_type'])

    # print('temp：', res)
    insert['cut_remarks'] = res.strip('()[]（）')
    # print('cut_remarks：', insert['cut_remarks'])

    # 根据院校代码，查询院校库university_2，是否存在，存在则取出该院校的所在省市地址
    university_row = university.getOneByCode(insert['ucode'])
    if university_row:
        insert['province_code'] = university_row['province_code']
        insert['city_code'] = university_row['city_code']
        insert['province'] = university_row['province']
        insert['city'] = university_row['city']
    else:
        insert['province_code'] = 0
        insert['city_code'] = 0
        insert['province'] = ''
        insert['city'] = address
    print("insert:", insert)
    return insert


def match_fun_2(remark_new, f, insert, address):
    university = University()
    num_people = f[-1]  # 匹配xx人之后，返回的f1是一个列表，取f1列表最后一个元素
    # print('num_people：', num_people)
    # print(remark_new.split(num_people))

    # 匹配专业
    major = remark_new.split(num_people)[0:1][0].strip('()[]（）')  # xx人之前，为专业。同时删除首尾的括号或者中括号
    # print('major：', major)
    if major:
        if insert['major_code'] and major.find(insert['major_code']) != -1:
            major = major.replace(insert['major_code'], '')
        else:
            # 匹配不到专业内部代码，则删除专业前的数字
            major.strip(major.digits)
    # print('major_final：', major)

    # 匹配专业文理科
    res = remark_new.split(major)[1:][0]  # 从 匹配到专业的地方，分割字符串为两半，然后去后一半
    if res.find('理工') != -1:
        insert['major_type'] = '理工类'
        insert['type'] = 1
    elif res.find('文史') != -1:
        insert['major_type'] = '文史类'
        insert['type'] = 2
    else:
        insert['major_type'] = ''
        insert['type'] = 0
    # print('专业类型：', insert['major_type'])

    print('temp：', res)

    insert['cut_remarks'] = res.strip('()[]（）')
    # print('cut_remarks：', insert['cut_remarks'])

    # 如果专业后面后“人”字，则删除
    insert['major'] = major.rstrip('人')

    # 根据院校代码，查询院校库university_2，是否存在，存在则取出该院校的所在省市地址
    university_row = university.getOneByCode(insert['ucode'])
    if university_row:
        insert['province_code'] = university_row['province_code']
        insert['city_code'] = university_row['city_code']
        insert['province'] = university_row['province']
        insert['city'] = university_row['city']
    else:
        insert['province_code'] = 0
        insert['city_code'] = 0
        insert['province'] = ''
        insert['city'] = address
    print("insert:", insert)
    return insert


# 检查完university_major_plan_temp表数据没大问题后，批量复制到正式表university_major_plan
def copy_final():
    universityMajorPlanTemp = UniversityMajorPlanTemp()
    universityMajorPlan = UniversityMajorPlan()
    year = 2023
    data = universityMajorPlanTemp.getListAll(year)
    total_list = list()
    for index, row in enumerate(data):
        print(row['id'])
        del row['id']
        total_list.append(row)

    universityMajorPlan.toInsertBatch(total_list)


def test():
    remark = '020人计算机类(国家网络安全学院)7人（理工类，4年，5850元/年，包含专业:信息安全、网络空间安全)'
    # 去除字符串所有空格
    remark_new = remark.replace(" ", "")
    # 匹配 xx人
    find_result = re.findall(r'(\d+人)', remark_new)
    if find_result:
        print('find_result：', find_result, find_result[-1][:-1])
        plan_number_temp = find_result[-1][:-1]
        print('首个数字：', plan_number_temp[0])
        if plan_number_temp and plan_number_temp[0] != '0':
            pass


# 更新招生计划人数
def update_plan_numbers():
    universityMajorPlan = UniversityMajorPlan()
    universityMajorRankMean = UniversityMajorRankMean()
    year = 2023
    data = universityMajorPlan.getListAll2(year)
    for index, row in enumerate(data):
        print(row)
        remark = row['remarks']
        # 去除字符串所有空格
        remark_new = remark.replace(" ", "")
        # 匹配 xx人
        find_result = re.findall(r'(\d+人)', remark_new)
        if find_result:
            # 匹配到xx人
            print('id: ', row['id'], 'find_result：', find_result, find_result[-1][:-1])
            plan_number = find_result[-1][:-1]  # 提取XX人字符串中的数字
            # 判断数字的首个字符是否为0，不为0则更新。因为有时会遇到 “023人力资源管理10人” 的情况
            if plan_number and plan_number[0] != '0':
                update_1 = dict()
                update_1['id'] = row['id']
                update_1['plan_number'] = plan_number
                print(update_1)
                universityMajorPlan.toUpdatePlanNumber(update_1)

                update_2 = dict()
                update_2['year'] = year
                update_2['batch'] = row['batch']
                update_2['type'] = row['type']
                update_2['code'] = row['ucode']
                update_2['major_code'] = row['major_code']
                update_2['plan_number'] = plan_number
                print(update_2)
                universityMajorRankMean.toUpdatePlanNumber(update_2)


if __name__ == "__main__":
    update_plan_numbers()
